1 DECLARE @base
nvarchar(max),
5 SET @base
= 'ALTER TABLE /*_*/archive DROP CONSTRAINT ';--
8 FROM sys.foreign_keys fk
9 JOIN sys.foreign_key_columns fkc
10 ON fkc.constraint_object_id
= fk.object_id
12 ON c.column_id
= fkc.parent_column_id
13 AND c.object_id
= fkc.parent_object_id
15 fk.parent_object_id
= OBJECT_ID('/*_*/archive')
16 AND fk.referenced_object_id
= OBJECT_ID('/*_*/revision')
17 AND c.
name = 'ar_parent_id';--
19 SET @
SQL = @base
+ @
id;--
21 EXEC sp_executesql @
SQL;--
23 -- while we're at it, let's fix up the other foreign key constraints on archive
24 -- as future patches touch constraints on other tables, they'll take the time to update constraint names there as well
26 FROM sys.foreign_keys fk
27 JOIN sys.foreign_key_columns fkc
28 ON fkc.constraint_object_id
= fk.object_id
30 ON c.column_id
= fkc.parent_column_id
31 AND c.object_id
= fkc.parent_object_id
33 fk.parent_object_id
= OBJECT_ID('/*_*/archive')
34 AND fk.referenced_object_id
= OBJECT_ID('/*_*/mwuser')
35 AND c.
name = 'ar_user';--
37 SET @
SQL = @base
+ @
id;--
39 EXEC sp_executesql @
SQL;--
41 ALTER TABLE /*_*/archive
ADD CONSTRAINT ar_user__user_id__fk
FOREIGN KEY (ar_user
) REFERENCES /*_*/mwuser(user_id
);--
44 FROM sys.foreign_keys fk
45 JOIN sys.foreign_key_columns fkc
46 ON fkc.constraint_object_id
= fk.object_id
48 ON c.column_id
= fkc.parent_column_id
49 AND c.object_id
= fkc.parent_object_id
51 fk.parent_object_id
= OBJECT_ID('/*_*/archive')
52 AND fk.referenced_object_id
= OBJECT_ID('/*_*/text')
53 AND c.
name = 'ar_text_id';--
55 SET @
SQL = @base
+ @
id;--
57 EXEC sp_executesql @
SQL;--
59 ALTER TABLE /*_*/archive
ADD CONSTRAINT ar_text_id__old_id__fk
FOREIGN KEY (ar_text_id
) REFERENCES /*_*/text(old_id
) ON DELETE CASCADE;